<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<!-- saved from url=(0059)https://www.oschina.net/translate/node-mysql-tutorial?print -->
<html xmlns="http://www.w3.org/1999/xhtml"><head><meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
    
    <meta name="referrer" content="always">
    <title>Node-MySQL 官方文档 - 技术翻译 - 开源中国社区</title>
    <link rel="stylesheet" href="./Node-MySQL 官方文档_files/default.css">
    <script src="./Node-MySQL 官方文档_files/jquery.min.js.下载"></script>
    <script src="./Node-MySQL 官方文档_files/highlight.pack.js.下载"></script>     <script type="text/javascript">
        $(document).ready(function () {
            $('pre, pre code').each(function (i, block) {
                var codeEl = $(this);
                var className = codeEl.attr('class') || '';
                var array = className.match(/language-(.*)/);
                if (array && array.length > 1) {
                    var language = array[array.length - 1];
                    codeEl.attr('class', 'hljs ' + language);
                }
                hljs.highlightBlock(block);
            });
        });
    </script>
    <style type="text/css">
        body {
            padding: 10px;
            margin: 0;
            background: white;
            color: #000000;
        }

        body, table, input, textarea, select {
            font-family: 微软雅黑, Verdana, sans-serif, 宋体;
        }

        a:link, a:visited {
            color: #000000;
            text-decoration: none;
            background: transparent;
        }

        img {
            1px solid #cccccc;
            background-color: #fff;
        }

        h1 {
            border-bottom: 2px solid #666;
            padding: 0 0 20px 0;
        }

        .TextContent {
            line-height: 1.2em;
        }

        .TextContent pre {
            margin: 10px 0;
            font-size: 9pt;
            font-family: Courier New, Arial
        }

        .TextContent code {
            margin: 0 2px;
            padding: 0 5px;
            white-space: nowrap;
        }

        .TextContent pre > code {
            margin: 0;
            padding: 0;
            white-space: pre;
            border: none;
            background: transparent;
        }

        .TextContent pre code {
            background-color: transparent;
            border: none;
        }

        .TextContent ol, .TextContent ul {
            margin: 20px 0 20px 20px;
        }

        .TextContent ol {
            list-style-type: decimal;
            margin: 0.5em 0 0.5em 1.5em;
        }

        .TextContent ul {
            list-style-type: disc;
            margin: 0.5em 0 0.5em 1.5em;
        }

        .TextContent img {
            max-width: 980px;
        }

        .links {
            margin-top: 1em;
            padding: 1.25em 0;
            color: #666;
        }

        .links a, .copyright a {
            color: #666;
        }

        .links p {
            margin: 0;
            padding: 0;
        }

        .copyright {
            padding: 1.25em 0;
            border-top: 1px solid #AAA;
            color: #666;
        }
    </style>
</head>
<body>
    <h1>Node-MySQL 官方文档</h1>
    <div class="TextContent">
                                                         <p><strong>安装</strong></p><pre class="brush:shell;toolbar: true; auto-links: false; hljs"><span class="hljs-meta">$</span><span class="bash">&nbsp;npm&nbsp;install&nbsp;mysql</span></pre><p>有关之前的0.9.x版本的信息, 请访问&nbsp;<a href="https://github.com/mysqljs/mysql/tree/v0.9" target="_blank" rel="nofollow">v0.9分支</a>。</p><p>有时我还会要求你从Github安装最新版以检查bug是否已修复。在这种情况下，请输入:</p><pre class="brush:shell;toolbar: true; auto-links: false; hljs"><span class="hljs-meta">$</span><span class="bash">&nbsp;npm&nbsp;install&nbsp;mysqljs/mysql</span></pre><p><strong>引言</strong></p><p>这是node.js的mysql驱动。它是用JavaScript编写的，不需要编译，完全遵循MIT许可协议。</p><p>下面是一个如何使用它的例子:</p><pre class="brush:js;toolbar: true; auto-links: false; hljs javascript"><span class="hljs-keyword">var</span>&nbsp;mysql&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;=&nbsp;<span class="hljs-built_in">require</span>(<span class="hljs-string">'mysql'</span>);
<span class="hljs-keyword">var</span>&nbsp;connection&nbsp;=&nbsp;mysql.createConnection({
&nbsp;&nbsp;<span class="hljs-attr">host</span>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;:&nbsp;<span class="hljs-string">'localhost'</span>,
&nbsp;&nbsp;<span class="hljs-attr">user</span>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;:&nbsp;<span class="hljs-string">'me'</span>,
&nbsp;&nbsp;<span class="hljs-attr">password</span>&nbsp;:&nbsp;<span class="hljs-string">'secret'</span>,
&nbsp;&nbsp;<span class="hljs-attr">database</span>&nbsp;:&nbsp;<span class="hljs-string">'my_db'</span>
});
connection.connect();
connection.query(<span class="hljs-string">'SELECT&nbsp;1&nbsp;+&nbsp;1&nbsp;AS&nbsp;solution'</span>,&nbsp;<span class="hljs-function"><span class="hljs-keyword">function</span>(<span class="hljs-params">err,&nbsp;rows,&nbsp;fields</span>)&nbsp;</span>{
&nbsp;&nbsp;<span class="hljs-keyword">if</span>&nbsp;(err)&nbsp;<span class="hljs-keyword">throw</span>&nbsp;err;
&nbsp;&nbsp;<span class="hljs-built_in">console</span>.log(<span class="hljs-string">'The&nbsp;solution&nbsp;is:&nbsp;'</span>,&nbsp;rows[<span class="hljs-number">0</span>].solution);
});
connection.end();</pre><p>从这个例子中，你可以了解到以下几点:</p><ul><li><p>对于一个连接，你所调用的每个方法都是按顺序排队并依次执行的。</p></li><li><p>使用end()关闭连接，以确保给mysql服务器发送退出（quit）包以前执行所有剩余的查询。</p></li></ul><p><strong>贡献者</strong></p><p>感谢那些给node-mysql模块贡献代码的人们, 参见<a href="https://github.com/mysqljs/mysql/graphs/contributors" target="_blank" rel="nofollow">GitHub 贡献者页面。</a></p><p>另外我还要感谢下面的两个人：</p><ul><li><p><a href="http://andrey.hristov.com/" target="_blank" rel="nofollow">Andrey Hristov</a> (Oracle) - 帮助我解决了一些协议问题。</p></li><li><p><a href="http://blog.ulf-wendel.de/" target="_blank" rel="nofollow">Ulf Wendel</a> (Oracle) -帮助我解决了一些协议问题。</p></li></ul><p><strong>赞助商</strong></p><p>下面这些公司给这个项目提供了一些经济支持，请允许我花更多的时间感谢它们（按贡献时间的先后排序）：</p><ul><li><p><a href="http://transloadit.com/" target="_blank" rel="nofollow">Transloadit</a> (项目初期，我们要将文件上传和视频编码做成一个服务，已完成)</p></li><li><p><a href="http://www.joyent.com/" target="_blank" rel="nofollow">Joyent</a></p></li><li><p><a href="http://pinkbike.com/" target="_blank" rel="nofollow">pinkbike.com</a></p></li><li><p><a href="http://www.holidayextras.co.uk/" target="_blank" rel="nofollow">Holiday Extras</a> (他们正在<a href="http://join.holidayextras.co.uk/" target="_blank" rel="nofollow">招聘</a>)</p></li><li><p><a href="http://newscope.com/" target="_blank" rel="nofollow">Newscope</a> (他们正在&nbsp;<a href="http://www.newscope.com/stellenangebote" target="_blank" rel="nofollow">招聘</a>）</p></li></ul><p>如果你有兴趣赞助一天或更多的时间, 请 <a href="http://felixge.de/#consulting" target="_blank" rel="nofollow">与我们联系</a>。</p>
                                                                                     <p><strong>社区生态</strong></p><p>如果你想讨论这个模块的有关问题，亦或是其它的问题，请访问以下的任意一个：</p><ul><li><p>Mailing list:&nbsp;<a href="https://groups.google.com/forum/#!forum/node-mysql" target="_blank" rel="nofollow">https://groups.google.com/forum/#!forum/node-mysql</a></p></li><li><p>IRC Channel: #node.js (我关了freenode.net上的mysql的相关信息)</p><p>如何建立链接</p></li></ul><p>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;推荐的建立链接的一个方法：</p><pre class="brush:js;toolbar: true; auto-links: false; hljs javascript"><span class="hljs-keyword">var</span>&nbsp;mysql&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;=&nbsp;<span class="hljs-built_in">require</span>(<span class="hljs-string">'mysql'</span>);
<span class="hljs-keyword">var</span>&nbsp;connection&nbsp;=&nbsp;mysql.createConnection({
&nbsp;&nbsp;<span class="hljs-attr">host</span>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;:&nbsp;<span class="hljs-string">'example.org'</span>,
&nbsp;&nbsp;<span class="hljs-attr">user</span>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;:&nbsp;<span class="hljs-string">'bob'</span>,
&nbsp;&nbsp;<span class="hljs-attr">password</span>&nbsp;:&nbsp;<span class="hljs-string">'secret'</span>
});
connection.connect(<span class="hljs-function"><span class="hljs-keyword">function</span>(<span class="hljs-params">err</span>)&nbsp;</span>{
&nbsp;&nbsp;<span class="hljs-keyword">if</span>&nbsp;(err)&nbsp;{
&nbsp;&nbsp;&nbsp;&nbsp;<span class="hljs-built_in">console</span>.error(<span class="hljs-string">'error&nbsp;connecting:&nbsp;'</span>&nbsp;+&nbsp;err.stack);
&nbsp;&nbsp;&nbsp;&nbsp;<span class="hljs-keyword">return</span>;
&nbsp;&nbsp;}
&nbsp;&nbsp;<span class="hljs-built_in">console</span>.log(<span class="hljs-string">'connected&nbsp;as&nbsp;id&nbsp;'</span>&nbsp;+&nbsp;connection.threadId);
});</pre><p>然后，我们可以通过建立一个连接来进行查询：</p><pre class="brush:js;toolbar: true; auto-links: false; hljs javascript"><span class="hljs-keyword">var</span>&nbsp;mysql&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;=&nbsp;<span class="hljs-built_in">require</span>(<span class="hljs-string">'mysql'</span>);
<span class="hljs-keyword">var</span>&nbsp;connection&nbsp;=&nbsp;mysql.createConnection(...);
connection.query(<span class="hljs-string">'SELECT&nbsp;1'</span>,&nbsp;<span class="hljs-function"><span class="hljs-keyword">function</span>(<span class="hljs-params">err,&nbsp;rows</span>)&nbsp;</span>{
&nbsp;&nbsp;<span class="hljs-comment">//&nbsp;connected!&nbsp;(unless&nbsp;`err`&nbsp;is&nbsp;set)</span>
});</pre><p>以上二种方法都是正确且合适的。至于如何取舍，就要看你怎么去处理所遇到的错误了。不管哪种类型的错误，那都是致命的，我们需要去看所提示的具体的错误信息。</p><p><strong>连接参数</strong></p><p>在建立新连接时，可以设置以下参数：</p><ul><li><p>host：连接的数据库地址。（默认:localhost）</p></li><li><p>port：连接地址对应的端口。（默认:3306）</p></li><li><p>localAddress:&nbsp;源IP地址使用TCP连接。（可选）</p></li><li><p>socketPath:当主机和端口参数被忽略的时候，可以填写一个Unix的Socket地址。<br></p></li><li><p>user: mysql的连接用户名。</p></li><li><p>password: 对应用户的密码。</p></li><li><p>database: 所需要连接的数据库的名称。（可选）<br></p></li><li><p>charset: 连接的编码形式。这就是mysql中的整理。（例如：utf8_general_ci）如果被指定，则作为默认的整理排序规则。（默认：utf8_general_ci）</p></li><li><p>timezone:用来保存当前本地的时区。（默认：local）<br></p></li><li><p>connectTimeout: 设置在连接的时候，超过多久以后未响应则返回失败。（默认：10000）</p></li><li><p>stringifyObjects:&nbsp;stringify对象代替转换值。issue# 501。（默认：false）<br></p></li><li><p>insecureAuth：使用旧（不安全）的连接方式去连接MySQL。（默认：false）<br></p></li><li><p>typeCast:&nbsp;确定列值是否需要转换为本地JavaScript类型。（默认：true）<br></p></li><li><p>queryFormat:自定义查询的方式。<a href="https://github.com/mysqljs/mysql#custom-format" target="_blank" rel="nofollow"></a>地址：<a href="https://github.com/mysqljs/mysql#custom-format" target="_blank" rel="nofollow">Custom format</a>.<br></p></li><li><p>supportBigNumbers: 如果你使用了BIGINT和DECIMAL格式的表列，那么需要开启这个参数来支持。（默认：false）只有当他们超过JavaScript所能表达的最长的字节的时候，如果没有设置这个参数，则会将过长的数字作为字符串传递。否则，返回对象的长度。如果supportBigNumbers参数被忽略，则这个参数也会被忽略。</p></li><li><p>dateStrings:一些日期类型(TIMESTAMP, DATETIME, DATE)会以Strings的类型返回，然后转换成JavaScript的日期对象。（默认：false）</p></li><li><p>debug:是否把连接情况打印到文件。（默认：false）</p></li><li><p>trace:&nbsp;生成错误的堆栈跟踪，包括库入口的调用位置（“长堆栈的轨迹”）。一般会造成轻微的性能损失。（默认：true）</p></li></ul>
                                                                                     <p><strong>终止连接</strong></p><p>终止连接的方法有两种。调用end()方法可以正常地终止一个连接：</p><pre class="brush:js;toolbar: true; auto-links: false; hljs javascript">connection.end(<span class="hljs-function"><span class="hljs-keyword">function</span>(<span class="hljs-params">err</span>)&nbsp;</span>{
&nbsp;&nbsp;<span class="hljs-comment">//&nbsp;连接终止</span>
});</pre><p>这种方法将确保给MySQL服务器发送COM_QUIT包之前所有队列中的查询都会被执行。如果在发送COM_QUIT包之前发生了致命错误，那么会给回调函数传递一个err参数，但是不管怎样连接都会关闭。</p><p>另外一种终止连接的方法是调用destroy()方法。该方法会立即终止底层套接字（underlying socket）。另外，destroy()不会触发更多的事件和回调函数。</p><pre class="brush:js;toolbar: true; auto-links: false; hljs css"><span class="hljs-selector-tag">connection</span><span class="hljs-selector-class">.destroy</span>();</pre><p>和end()方法不同，destroy()方法不使用回调参数。</p><p><strong>连接池连接</strong></p><p>直接使用连接池。</p><pre class="brush:js;toolbar: true; auto-links: false; hljs javascript"><span class="hljs-keyword">var</span>&nbsp;mysql&nbsp;=&nbsp;<span class="hljs-built_in">require</span>(<span class="hljs-string">'mysql'</span>);
<span class="hljs-keyword">var</span>&nbsp;pool&nbsp;&nbsp;=&nbsp;mysql.createPool({
&nbsp;&nbsp;<span class="hljs-attr">connectionLimit</span>&nbsp;:&nbsp;<span class="hljs-number">10</span>,
&nbsp;&nbsp;<span class="hljs-attr">host</span>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;:&nbsp;<span class="hljs-string">'example.org'</span>,
&nbsp;&nbsp;<span class="hljs-attr">user</span>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;:&nbsp;<span class="hljs-string">'bob'</span>,
&nbsp;&nbsp;<span class="hljs-attr">password</span>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;:&nbsp;<span class="hljs-string">'secret'</span>,
&nbsp;&nbsp;<span class="hljs-attr">database</span>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;:&nbsp;<span class="hljs-string">'my_db'</span>
});
pool.query(<span class="hljs-string">'SELECT&nbsp;1&nbsp;+&nbsp;1&nbsp;AS&nbsp;solution'</span>,&nbsp;<span class="hljs-function"><span class="hljs-keyword">function</span>(<span class="hljs-params">err,&nbsp;rows,&nbsp;fields</span>)&nbsp;</span>{
&nbsp;&nbsp;<span class="hljs-keyword">if</span>&nbsp;(err)&nbsp;<span class="hljs-keyword">throw</span>&nbsp;err;
&nbsp;&nbsp;<span class="hljs-built_in">console</span>.log(<span class="hljs-string">'The&nbsp;solution&nbsp;is:&nbsp;'</span>,&nbsp;rows[<span class="hljs-number">0</span>].solution);
});</pre><p>使用连接池连接可以更容易地共享某个连接，也可以管理多个连接。</p><pre class="brush:js;toolbar: true; auto-links: false; hljs javascript"><span class="hljs-keyword">var</span>&nbsp;mysql&nbsp;=&nbsp;<span class="hljs-built_in">require</span>(<span class="hljs-string">'mysql'</span>);
<span class="hljs-keyword">var</span>&nbsp;pool&nbsp;&nbsp;=&nbsp;mysql.createPool({
&nbsp;&nbsp;<span class="hljs-attr">host</span>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;:&nbsp;<span class="hljs-string">'example.org'</span>,
&nbsp;&nbsp;<span class="hljs-attr">user</span>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;:&nbsp;<span class="hljs-string">'bob'</span>,
&nbsp;&nbsp;<span class="hljs-attr">password</span>&nbsp;:&nbsp;<span class="hljs-string">'secret'</span>,
&nbsp;&nbsp;<span class="hljs-attr">database</span>&nbsp;:&nbsp;<span class="hljs-string">'my_db'</span>
});
pool.getConnection(<span class="hljs-function"><span class="hljs-keyword">function</span>(<span class="hljs-params">err,&nbsp;connection</span>)&nbsp;</span>{
&nbsp;&nbsp;<span class="hljs-comment">//&nbsp;connected!&nbsp;(unless&nbsp;`err`&nbsp;is&nbsp;set)</span>
});</pre><p>当连接完成后，调用connection.release()方法使连接返回到连接池，以便其他人可以再次使用。</p><pre class="brush:js;toolbar: true; auto-links: false; hljs javascript"><span class="hljs-keyword">var</span>&nbsp;mysql&nbsp;=&nbsp;<span class="hljs-built_in">require</span>(<span class="hljs-string">'mysql'</span>);
<span class="hljs-keyword">var</span>&nbsp;pool&nbsp;&nbsp;=&nbsp;mysql.createPool(...);
pool.getConnection(<span class="hljs-function"><span class="hljs-keyword">function</span>(<span class="hljs-params">err,&nbsp;connection</span>)&nbsp;</span>{
&nbsp;&nbsp;<span class="hljs-comment">//&nbsp;使用连接</span>
&nbsp;&nbsp;connection.query(&nbsp;<span class="hljs-string">'SELECT&nbsp;something&nbsp;FROM&nbsp;sometable'</span>,&nbsp;<span class="hljs-function"><span class="hljs-keyword">function</span>(<span class="hljs-params">err,&nbsp;rows</span>)&nbsp;</span>{
&nbsp;&nbsp;&nbsp;&nbsp;<span class="hljs-comment">//&nbsp;使用连接执行查询</span>
&nbsp;&nbsp;&nbsp;&nbsp;connection.release();
&nbsp;&nbsp;&nbsp;&nbsp;<span class="hljs-comment">//连接不再使用，返回到连接池</span>
&nbsp;&nbsp;});
});</pre><p>如果你想关闭连接并从连接池中删除它，就要使用connection.destroy()方法。在下次需要时连接池会再创建一个新的连接。</p><p>连接池创建连接很慵懒。如果你配置的连接池最大支持100个连接，但同时只使用了5个连接，那么它只创建5个连接。连接还采用了循环轮转方式，即连接建立在连接池顶部，返回到连接池底部。</p><p>当从连接池中恢复之前的某个连接时，会给服务器发送一个ping包以检查连接是否正常。</p>
                                                                                     <p>
	<strong>连接池的配置参数</strong>
</p>
<p>
	连接池接受所有与连接相同的<a target="_blank" href="https://github.com/mysqljs/mysql#connection-options" rel="nofollow">配置参数</a>。创建新连接时，配置参数只是简单的传给连接对象的构造器。除此配置外，连接池还支持一些额外的参数：
</p>
<ul>
	<li>
		<p>
			acquireTimeout（获取超时时间）: 获取连接时，触发连接超时之前的毫秒数。这与connectTimeout略有不同，因为从连接池获取连接并不总会创建连接 （默认值：10000）
		</p>
	</li>
	<li>
		<p>
			waitForConnections(连接等待时间）: &nbsp;当无连接可用或连接数达到上限的时候，判定连接池动作。如果为true，连接池会将请求加入队列，待可用之时再触发操作；如为false，连接池将立即返回错误 （默认值：true)&nbsp;&nbsp;&nbsp;
		</p>
	</li>
	<li>
		<p>
			connectionLimit(连接数限制): 所允许立即创建的最大连接数量 (默认值: 10)
		</p>
	</li>
	<li>
		<p>
			queueLimit（队列数量限制）: 在调用getConnection返回错误之前，连接池所允许入队列的最大请求数量。如设置为0， 则不限制。 (默认值: 0)
		</p>
	</li>
</ul>
<p>
	<strong>连接池的事件</strong>
</p>
<p>
	<strong>连接</strong>
</p>
<p>
	连接池里面创建了一个新连接时，会触发一个连接事件。如需要在使用此连接之前设置会话变量，将要对此事件进行监听。
</p>
<pre class="brush:js;toolbar: true; auto-links: false; hljs javascript">pool.on(<span class="hljs-string">'connection'</span>,&nbsp;<span class="hljs-function"><span class="hljs-keyword">function</span>&nbsp;(<span class="hljs-params">connection</span>)&nbsp;</span>{
&nbsp;&nbsp;connection.query(<span class="hljs-string">'SET&nbsp;SESSION&nbsp;auto_increment_increment=1'</span>)
});</pre>
<p>
	<strong>入列</strong>
</p>
<p>
	队列中等待可用连接的回调函数被触发时，连接池将触发此事件。
</p>
<pre class="brush:js;toolbar: true; auto-links: false; hljs javascript">pool.on(<span class="hljs-string">'enqueue'</span>,&nbsp;<span class="hljs-function"><span class="hljs-keyword">function</span>&nbsp;(<span class="hljs-params"></span>)&nbsp;</span>{
&nbsp;&nbsp;<span class="hljs-built_in">console</span>.log(<span class="hljs-string">'Waiting&nbsp;for&nbsp;available&nbsp;connection&nbsp;slot'</span>);
});</pre>
<p>
	<strong>在连接池中关闭所有连接</strong>
</p>
<p>
	如不再需要连接池时，你必须关闭所有连接。否则Node.js脚本的事件流一直处于活跃状态，最终会被MySQL服务器会关闭。脚本占用连接池，这是典型的情况！另外可以以优雅的方式的关闭服务器。关闭所有连接池中的连接，中使用end方法：
</p>
<pre class="brush:js;toolbar: true; auto-links: false; hljs javascript">pool.end(<span class="hljs-function"><span class="hljs-keyword">function</span>&nbsp;(<span class="hljs-params">err</span>)&nbsp;</span>{
&nbsp;&nbsp;<span class="hljs-comment">//&nbsp;all&nbsp;connections&nbsp;in&nbsp;the&nbsp;pool&nbsp;have&nbsp;ended</span>
});</pre>
<p>
	end方法接受一个可选的回调函数，以通知（一次）所有已关闭的连接。优雅关闭连接，队列中所有的查询都能被执行，但关闭连接池的时间会不一样。
</p>
<p>
	一旦pool.end()被调用，pool.getConnection及其它操作将不再被执行！
</p>
                                                                                     <p><strong>集群连接池</strong></p><p>
	集群连接池提供多主机连接.(分组&amp;重试&amp;选择器)</p><pre class="brush:js;toolbar: true; auto-links: false; hljs javascript"><span class="hljs-comment">//&nbsp;create</span>
<span class="hljs-keyword">var</span>&nbsp;poolCluster&nbsp;=&nbsp;mysql.createPoolCluster();
<span class="hljs-comment">//&nbsp;add&nbsp;configurations&nbsp;(the&nbsp;config&nbsp;is&nbsp;a&nbsp;pool&nbsp;config&nbsp;object)</span>
poolCluster.add(config);&nbsp;<span class="hljs-comment">//&nbsp;add&nbsp;configuration&nbsp;with&nbsp;automatic&nbsp;name</span>
poolCluster.add(<span class="hljs-string">'MASTER'</span>,&nbsp;masterConfig);&nbsp;<span class="hljs-comment">//&nbsp;add&nbsp;a&nbsp;named&nbsp;configuration</span>
poolCluster.add(<span class="hljs-string">'SLAVE1'</span>,&nbsp;slave1Config);
poolCluster.add(<span class="hljs-string">'SLAVE2'</span>,&nbsp;slave2Config);
<span class="hljs-comment">//&nbsp;remove&nbsp;configurations</span>
poolCluster.remove(<span class="hljs-string">'SLAVE2'</span>);&nbsp;<span class="hljs-comment">//&nbsp;By&nbsp;nodeId</span>
poolCluster.remove(<span class="hljs-string">'SLAVE*'</span>);&nbsp;<span class="hljs-comment">//&nbsp;By&nbsp;target&nbsp;group&nbsp;:&nbsp;SLAVE1-2</span>
<span class="hljs-comment">//&nbsp;Target&nbsp;Group&nbsp;:&nbsp;ALL(anonymous,&nbsp;MASTER,&nbsp;SLAVE1-2),&nbsp;Selector&nbsp;:&nbsp;round-robin(default)</span>
poolCluster.getConnection(<span class="hljs-function"><span class="hljs-keyword">function</span>&nbsp;(<span class="hljs-params">err,&nbsp;connection</span>)&nbsp;</span>{});
<span class="hljs-comment">//&nbsp;Target&nbsp;Group&nbsp;:&nbsp;MASTER,&nbsp;Selector&nbsp;:&nbsp;round-robin</span>
poolCluster.getConnection(<span class="hljs-string">'MASTER'</span>,&nbsp;<span class="hljs-function"><span class="hljs-keyword">function</span>&nbsp;(<span class="hljs-params">err,&nbsp;connection</span>)&nbsp;</span>{});
<span class="hljs-comment">//&nbsp;Target&nbsp;Group&nbsp;:&nbsp;SLAVE1-2,&nbsp;Selector&nbsp;:&nbsp;order</span>
<span class="hljs-comment">//&nbsp;If&nbsp;can't&nbsp;connect&nbsp;to&nbsp;SLAVE1,&nbsp;return&nbsp;SLAVE2.&nbsp;(remove&nbsp;SLAVE1&nbsp;in&nbsp;the&nbsp;cluster)</span>
poolCluster.on(<span class="hljs-string">'remove'</span>,&nbsp;<span class="hljs-function"><span class="hljs-keyword">function</span>&nbsp;(<span class="hljs-params">nodeId</span>)&nbsp;</span>{
&nbsp;&nbsp;<span class="hljs-built_in">console</span>.log(<span class="hljs-string">'REMOVED&nbsp;NODE&nbsp;:&nbsp;'</span>&nbsp;+&nbsp;nodeId);&nbsp;<span class="hljs-comment">//&nbsp;nodeId&nbsp;=&nbsp;SLAVE1&nbsp;</span>
});
poolCluster.getConnection(<span class="hljs-string">'SLAVE*'</span>,&nbsp;<span class="hljs-string">'ORDER'</span>,&nbsp;<span class="hljs-function"><span class="hljs-keyword">function</span>&nbsp;(<span class="hljs-params">err,&nbsp;connection</span>)&nbsp;</span>{});
<span class="hljs-comment">//&nbsp;of&nbsp;namespace&nbsp;:&nbsp;of(pattern,&nbsp;selector)</span>
poolCluster.of(<span class="hljs-string">'*'</span>).getConnection(<span class="hljs-function"><span class="hljs-keyword">function</span>&nbsp;(<span class="hljs-params">err,&nbsp;connection</span>)&nbsp;</span>{});
<span class="hljs-keyword">var</span>&nbsp;pool&nbsp;=&nbsp;poolCluster.of(<span class="hljs-string">'SLAVE*'</span>,&nbsp;<span class="hljs-string">'RANDOM'</span>);
pool.getConnection(<span class="hljs-function"><span class="hljs-keyword">function</span>&nbsp;(<span class="hljs-params">err,&nbsp;connection</span>)&nbsp;</span>{});
pool.getConnection(<span class="hljs-function"><span class="hljs-keyword">function</span>&nbsp;(<span class="hljs-params">err,&nbsp;connection</span>)&nbsp;</span>{});
<span class="hljs-comment">//&nbsp;close&nbsp;all&nbsp;connections</span>
poolCluster.end(<span class="hljs-function"><span class="hljs-keyword">function</span>&nbsp;(<span class="hljs-params">err</span>)&nbsp;</span>{
&nbsp;&nbsp;<span class="hljs-comment">//&nbsp;all&nbsp;connections&nbsp;in&nbsp;the&nbsp;pool&nbsp;cluster&nbsp;have&nbsp;ended</span>
});</pre><p><strong><strong>集群连接池</strong>选项</strong></p><ul><li><p>
			canRetry: 如果为true,当连接失效时,集群连接池将尝试重新连接.&nbsp;&nbsp;(默认: true)		</p></li><li><p>
			removeNodeErrorCount: 如果连接失效,节点的错误次数增加.当错误次数大于removeNodeErrorCount时,该节点从集群链接池中移除&nbsp;(默认: 5)		</p></li><li><p>
			restoreNodeTimeout: 如果连接失效,该参数用于指定尝试获取另一个连接时需要等待的时间，时间单位为毫秒.如果为0,这个节点将被移除并且不会被重复使用.(默认值：0).		</p></li><li><p>
			defaultSelector: 默认选择器（默认值：RR）		</p></li><li><p>
			RR: 以轮询的方式选择节点. (轮询制)		</p></li><li><p>
			RANDOM: 使用随机函数选择节点.		</p></li><li><p>
			ORDER: 无条件地选择第一个可用节点.		</p></li></ul><pre class="brush:js;toolbar: true; auto-links: false; hljs javascript"><span class="hljs-keyword">var</span>&nbsp;clusterConfig&nbsp;=&nbsp;{
&nbsp;&nbsp;<span class="hljs-attr">removeNodeErrorCount</span>:&nbsp;<span class="hljs-number">1</span>,&nbsp;<span class="hljs-comment">//&nbsp;Remove&nbsp;the&nbsp;node&nbsp;immediately&nbsp;when&nbsp;connection&nbsp;fails.</span>
&nbsp;&nbsp;defaultSelector:&nbsp;<span class="hljs-string">'ORDER'</span>
};
<span class="hljs-keyword">var</span>&nbsp;poolCluster&nbsp;=&nbsp;mysql.createPoolCluster(clusterConfig);</pre><p><strong>更换用户并且改变连接状态</strong></p><p>
	MySQL 提供了一个<span style="font-size:13.3333px;">changeUser 命令，该命令允许你在不关闭下列socket的情况下，改变当前用户和连接的其余部分.</span></p><pre class="brush:js;toolbar: true; auto-links: false; hljs javascript">connection.changeUser({<span class="hljs-attr">user</span>&nbsp;:&nbsp;<span class="hljs-string">'john'</span>},&nbsp;<span class="hljs-function"><span class="hljs-keyword">function</span>(<span class="hljs-params">err</span>)&nbsp;</span>{
&nbsp;&nbsp;<span class="hljs-keyword">if</span>&nbsp;(err)&nbsp;<span class="hljs-keyword">throw</span>&nbsp;err;
});</pre><p>
	该特性的可用选项有：</p><ul><li><p>
			user: 新的用户名&nbsp;(默认前一次使用的用户名).		</p></li><li><p>
			password: 新用户的密码(默认前一次使用的密码).		</p></li><li><p>
			charset: 新的字符集&nbsp;(默认前一次使用的字符集).		</p></li><li><p>
			database: 新的数据库名称(默认前一次使用的数据库名).		</p></li></ul><p>
	有时有用的是这个功能的副作用,即会重置任何连接的状态（变量，事物等）.</p><p>
	在集群连接池模式下执行changeUser操作如果遇到错误将被视为致命错误.</p><p><strong>服务断开连接</strong></p><p>
	由于网络问题、<span style="font-size:13.3333px;">MySQL服务器重启或崩溃,连接超时等问题,</span>你有可能会断开与MySQL服务器的连接.所有上述事件被认定为致命错误,并且将返回错误码<span style="font-size:13.3333px;">'PROTOCOL_CONNECTION_LOST'.查看更多信息，请参照错误处理章节.</span></p><p>
	下一个连接将以建立一个新的连接的方式来获取.当前设计下,一旦终止,现有的一个连接对象将不能够被重连.</p><p>
	连接池状态下,端开的链接将会从连接池中移除并释放空间,<span style="font-size:13.3333px;">下一次调用getConnection时将用</span>释放的空间创建新连接。</p>
                                                                                     <p><strong>执行查询</strong></p><p>最基本的执行查询的方法是在一个对象中调用 .query()函数 (比如一个连接或应用池实例)。</p><p>最简单的 .query()形式是 .query(sqlString, callback)，第一个参数是一条SQL字符串，第二个参数是回调：</p><pre class="brush:js;toolbar: true; auto-links: false; hljs javascript">connection.query(<span class="hljs-string">'SELECT&nbsp;*&nbsp;FROM&nbsp;`books`&nbsp;WHERE&nbsp;`author`&nbsp;=&nbsp;"David"'</span>,&nbsp;<span class="hljs-function"><span class="hljs-keyword">function</span>&nbsp;(<span class="hljs-params">error,&nbsp;results,&nbsp;fields</span>)&nbsp;</span>{
&nbsp;&nbsp;<span class="hljs-comment">//&nbsp;error&nbsp;will&nbsp;be&nbsp;an&nbsp;Error&nbsp;if&nbsp;one&nbsp;occurred&nbsp;during&nbsp;the&nbsp;query</span>
&nbsp;&nbsp;<span class="hljs-comment">//&nbsp;results&nbsp;will&nbsp;contain&nbsp;the&nbsp;results&nbsp;of&nbsp;the&nbsp;query</span>
&nbsp;&nbsp;<span class="hljs-comment">//&nbsp;fields&nbsp;will&nbsp;contain&nbsp;information&nbsp;about&nbsp;the&nbsp;returned&nbsp;results&nbsp;fields&nbsp;(if&nbsp;any)</span>
});</pre><p>第二个.query()形式是 .query(sqlString, values, callback)，带有值的占位符 (查看转义查询值):</p><pre class="brush:js;toolbar: true; auto-links: false; hljs javascript">connection.query(<span class="hljs-string">'SELECT&nbsp;*&nbsp;FROM&nbsp;`books`&nbsp;WHERE&nbsp;`author`&nbsp;=&nbsp;?'</span>,&nbsp;[<span class="hljs-string">'David'</span>],&nbsp;<span class="hljs-function"><span class="hljs-keyword">function</span>&nbsp;(<span class="hljs-params">error,&nbsp;results,&nbsp;fields</span>)&nbsp;</span>{
&nbsp;&nbsp;<span class="hljs-comment">//&nbsp;error&nbsp;will&nbsp;be&nbsp;an&nbsp;Error&nbsp;if&nbsp;one&nbsp;occurred&nbsp;during&nbsp;the&nbsp;query</span>
&nbsp;&nbsp;<span class="hljs-comment">//&nbsp;results&nbsp;will&nbsp;contain&nbsp;the&nbsp;results&nbsp;of&nbsp;the&nbsp;query</span>
&nbsp;&nbsp;<span class="hljs-comment">//&nbsp;fields&nbsp;will&nbsp;contain&nbsp;information&nbsp;about&nbsp;the&nbsp;returned&nbsp;results&nbsp;fields&nbsp;(if&nbsp;any)</span>
});</pre><p>&nbsp; &nbsp;
	第三种 .query()形式是 .query(options, callback)，在查询时带有大量的高级可选项，比如 <a href="https://github.com/mysqljs/mysql#escaping-query-values" target="_blank" rel="nofollow">转义查询值(escaping query values)</a>，<a href="https://github.com/mysqljs/mysql#joins-with-overlapping-column-names" target="_blank" rel="nofollow">联结重叠列名(joins with overlapping column names)</a>，超时(timeouts), 和 <a href="https://github.com/mysqljs/mysql#type-casting" target="_blank" rel="nofollow">类型转换(type casting)</a>。</p><pre class="brush:js;toolbar: true; auto-links: false; hljs javascript">connection.query({
&nbsp;&nbsp;<span class="hljs-attr">sql</span>:&nbsp;<span class="hljs-string">'SELECT&nbsp;*&nbsp;FROM&nbsp;`books`&nbsp;WHERE&nbsp;`author`&nbsp;=&nbsp;?'</span>,
&nbsp;&nbsp;<span class="hljs-attr">timeout</span>:&nbsp;<span class="hljs-number">40000</span>,&nbsp;<span class="hljs-comment">//&nbsp;40s</span>
&nbsp;&nbsp;values:&nbsp;[<span class="hljs-string">'David'</span>]
},&nbsp;<span class="hljs-function"><span class="hljs-keyword">function</span>&nbsp;(<span class="hljs-params">error,&nbsp;results,&nbsp;fields</span>)&nbsp;</span>{
&nbsp;&nbsp;<span class="hljs-comment">//&nbsp;error&nbsp;will&nbsp;be&nbsp;an&nbsp;Error&nbsp;if&nbsp;one&nbsp;occurred&nbsp;during&nbsp;the&nbsp;query</span>
&nbsp;&nbsp;<span class="hljs-comment">//&nbsp;results&nbsp;will&nbsp;contain&nbsp;the&nbsp;results&nbsp;of&nbsp;the&nbsp;query</span>
&nbsp;&nbsp;<span class="hljs-comment">//&nbsp;fields&nbsp;will&nbsp;contain&nbsp;information&nbsp;about&nbsp;the&nbsp;returned&nbsp;results&nbsp;fields&nbsp;(if&nbsp;any)</span>
});</pre><p>注意一种第二和第三形式的结合体也可使用，其中占位符以参数来传递，并且不能做为选项对象。值参数会覆盖选项对象中的值。</p><pre class="brush:js;toolbar: true; auto-links: false; hljs javascript">connection.query({
&nbsp;&nbsp;&nbsp;&nbsp;<span class="hljs-attr">sql</span>:&nbsp;<span class="hljs-string">'SELECT&nbsp;*&nbsp;FROM&nbsp;`books`&nbsp;WHERE&nbsp;`author`&nbsp;=&nbsp;?'</span>,
&nbsp;&nbsp;&nbsp;&nbsp;<span class="hljs-attr">timeout</span>:&nbsp;<span class="hljs-number">40000</span>,&nbsp;<span class="hljs-comment">//&nbsp;40s</span>
&nbsp;&nbsp;},
&nbsp;&nbsp;[<span class="hljs-string">'David'</span>],
&nbsp;&nbsp;<span class="hljs-function"><span class="hljs-keyword">function</span>&nbsp;(<span class="hljs-params">error,&nbsp;results,&nbsp;fields</span>)&nbsp;</span>{
&nbsp;&nbsp;&nbsp;&nbsp;<span class="hljs-comment">//&nbsp;error&nbsp;will&nbsp;be&nbsp;an&nbsp;Error&nbsp;if&nbsp;one&nbsp;occurred&nbsp;during&nbsp;the&nbsp;query</span>
&nbsp;&nbsp;&nbsp;&nbsp;<span class="hljs-comment">//&nbsp;results&nbsp;will&nbsp;contain&nbsp;the&nbsp;results&nbsp;of&nbsp;the&nbsp;query</span>
&nbsp;&nbsp;&nbsp;&nbsp;<span class="hljs-comment">//&nbsp;fields&nbsp;will&nbsp;contain&nbsp;information&nbsp;about&nbsp;the&nbsp;returned&nbsp;results&nbsp;fields&nbsp;(if&nbsp;any)</span>
&nbsp;&nbsp;}
);</pre>
                                                                                     <p><strong>查询值转义</strong></p><p>
	为了防止SQL注入，每当需要在SQL查询中使用用户数据时，你都应当<span style="font-size:13.3333px;line-height:20px;">提前对这些值进行转义。转义可以通过&nbsp;</span>mysql.escape(), connection.escape() 或 pool.escape() 方法实现：</p><pre class="brush:js;toolbar: true; auto-links: false; hljs javascript"><span class="hljs-keyword">var</span>&nbsp;userId&nbsp;=&nbsp;<span class="hljs-string">'some&nbsp;user&nbsp;provided&nbsp;value'</span>;
<span class="hljs-keyword">var</span>&nbsp;sql&nbsp;&nbsp;&nbsp;&nbsp;=&nbsp;<span class="hljs-string">'SELECT&nbsp;*&nbsp;FROM&nbsp;users&nbsp;WHERE&nbsp;id&nbsp;=&nbsp;'</span>&nbsp;+&nbsp;connection.escape(userId);
connection.query(sql,&nbsp;<span class="hljs-function"><span class="hljs-keyword">function</span>(<span class="hljs-params">err,&nbsp;results</span>)&nbsp;</span>{
&nbsp;&nbsp;<span class="hljs-comment">//&nbsp;...</span>
});</pre><p>
	另外，也可以使用 ? 作为查询字符串中的占位符，替代你想要转义的值。例如：</p><pre class="brush:js;toolbar: true; auto-links: false; hljs javascript">connection.query(<span class="hljs-string">'SELECT&nbsp;*&nbsp;FROM&nbsp;users&nbsp;WHERE&nbsp;id&nbsp;=&nbsp;?'</span>,&nbsp;[userId],&nbsp;<span class="hljs-function"><span class="hljs-keyword">function</span>(<span class="hljs-params">err,&nbsp;results</span>)&nbsp;</span>{
&nbsp;&nbsp;<span class="hljs-comment">//&nbsp;...</span>
});</pre><p>
	使用多个占位符则传入的值会依次填入。例如，下方查询中foo将等于a、bar<span style="font-size:13.3333px;">将</span>等于b、baz<span style="font-size:13.3333px;">将</span>等于c，而id将会被赋值为userId的值：</p><pre class="brush:js;toolbar: true; auto-links: false; hljs javascript">connection.query(<span class="hljs-string">'UPDATE&nbsp;users&nbsp;SET&nbsp;foo&nbsp;=&nbsp;?,&nbsp;bar&nbsp;=&nbsp;?,&nbsp;baz&nbsp;=&nbsp;?&nbsp;WHERE&nbsp;id&nbsp;=&nbsp;?'</span>,&nbsp;[<span class="hljs-string">'a'</span>,&nbsp;<span class="hljs-string">'b'</span>,&nbsp;<span class="hljs-string">'c'</span>,&nbsp;userId],&nbsp;<span class="hljs-function"><span class="hljs-keyword">function</span>(<span class="hljs-params">err,&nbsp;results</span>)&nbsp;</span>{
&nbsp;&nbsp;<span class="hljs-comment">//&nbsp;...</span>
});</pre><p>
	这样看起来与MySQL中prepared statements相似，但它内部其实使用了之前的<span style="font-size:13.3333px;">connection.escape() 方法。</span></p><p><strong>注意</strong>&nbsp;它与prepared statements的另一点不同之处是，<span style="font-size:13.3333px;">即使是在注释和字符串中的&nbsp;</span>? 也会被替换。</p><p>
	不同类型的值转义的方式是有区别的，其区别如下：</p><ul><li><p>
			数字不会被转义		</p></li><li><p>
			布尔值会被转移成 true / false		</p></li><li><p>
			Date 对象会被转义成形如 'YYYY-mm-dd HH:ii:ss' 的字符串		</p></li><li><p>
			Buffer 会被转义成十六进制字符串，如： X'0fa5'		</p></li><li><p>
			字符串会被安全地转义		</p></li><li><p>
			数组会被转义成列表，例如： ['a', 'b'] 会被转义成 'a', 'b'		</p></li><li><p>
			嵌套数组会被转义成多个列表（在大规模插入时），如： [['a', 'b'], ['c', 'd']] 会被转义成 ('a', 'b'), ('c', 'd')		</p></li><li><p>
			对象的所有可遍历属性会被转义成键值对。如果属性的值是函数，则会被忽略；如果属性值是对象，则会使用其&nbsp;toString() 方法的返回值。		</p></li><li><p>
			undefined / null 会被转义成 NULL		</p></li><li><p>
			NaN / Infinity 将会被原样传入。由于MySQL 并不支持这些值，在它们得到支持之前，插入这些值将会导致MySQL报错。		</p></li></ul><p>
	如果你足够细心，可能已经注意到了，这种占位符转义可以写出<span style="font-size:13.3333px;">简洁的</span>代码：</p><pre class="brush:js;toolbar: true; auto-links: false; hljs javascript"><span class="hljs-keyword">var</span>&nbsp;post&nbsp;&nbsp;=&nbsp;{<span class="hljs-attr">id</span>:&nbsp;<span class="hljs-number">1</span>,&nbsp;<span class="hljs-attr">title</span>:&nbsp;<span class="hljs-string">'Hello&nbsp;MySQL'</span>};
<span class="hljs-keyword">var</span>&nbsp;query&nbsp;=&nbsp;connection.query(<span class="hljs-string">'INSERT&nbsp;INTO&nbsp;posts&nbsp;SET&nbsp;?'</span>,&nbsp;post,&nbsp;<span class="hljs-function"><span class="hljs-keyword">function</span>(<span class="hljs-params">err,&nbsp;result</span>)&nbsp;</span>{
&nbsp;&nbsp;<span class="hljs-comment">//&nbsp;Neat!</span>
});
<span class="hljs-built_in">console</span>.log(query.sql);&nbsp;<span class="hljs-comment">//&nbsp;INSERT&nbsp;INTO&nbsp;posts&nbsp;SET&nbsp;`id`&nbsp;=&nbsp;1,&nbsp;`title`&nbsp;=&nbsp;'Hello&nbsp;MySQL'</span></pre><p>
	当你觉得有必要亲自对这些查询进行转义时，也可以直接使用转义方法：</p><pre class="brush:js;toolbar: true; auto-links: false; hljs javascript"><span class="hljs-keyword">var</span>&nbsp;query&nbsp;=&nbsp;<span class="hljs-string">"SELECT&nbsp;*&nbsp;FROM&nbsp;posts&nbsp;WHERE&nbsp;title="</span>&nbsp;+&nbsp;mysql.escape(<span class="hljs-string">"Hello&nbsp;MySQL"</span>);
<span class="hljs-built_in">console</span>.log(query);&nbsp;<span class="hljs-comment">//&nbsp;SELECT&nbsp;*&nbsp;FROM&nbsp;posts&nbsp;WHERE&nbsp;title='Hello&nbsp;MySQL'</span></pre><p><strong>查询标识符转义</strong></p><p>
	如果用户提供了不可信的查询标识符（数据库名、表名、列名），你应该用 <span style="font-size:13.3333px;">mysql.escapeId(identifier), connection.escapeId(identifier) </span>或 <span style="font-size:13.3333px;">pool.escapeId(identifier)</span>&nbsp;方法对它进行转义，如：</p><pre class="brush:js;toolbar: true; auto-links: false; hljs javascript"><span class="hljs-keyword">var</span>&nbsp;sorter&nbsp;=&nbsp;<span class="hljs-string">'date'</span>;
<span class="hljs-keyword">var</span>&nbsp;sql&nbsp;&nbsp;&nbsp;&nbsp;=&nbsp;<span class="hljs-string">'SELECT&nbsp;*&nbsp;FROM&nbsp;posts&nbsp;ORDER&nbsp;BY&nbsp;'</span>&nbsp;+&nbsp;connection.escapeId(sorter);
connection.query(sql,&nbsp;<span class="hljs-function"><span class="hljs-keyword">function</span>(<span class="hljs-params">err,&nbsp;results</span>)&nbsp;</span>{
&nbsp;&nbsp;<span class="hljs-comment">//&nbsp;...</span>
});</pre><p>
	同时它还支持限定符，并对限定符两边的内容都进行转义。</p><pre class="brush:js;toolbar: true; auto-links: false; hljs javascript"><span class="hljs-keyword">var</span>&nbsp;sorter&nbsp;=&nbsp;<span class="hljs-string">'date'</span>;
<span class="hljs-keyword">var</span>&nbsp;sql&nbsp;&nbsp;&nbsp;&nbsp;=&nbsp;<span class="hljs-string">'SELECT&nbsp;*&nbsp;FROM&nbsp;posts&nbsp;ORDER&nbsp;BY&nbsp;'</span>&nbsp;+&nbsp;connection.escapeId(<span class="hljs-string">'posts.'</span>&nbsp;+&nbsp;sorter);
connection.query(sql,&nbsp;<span class="hljs-function"><span class="hljs-keyword">function</span>(<span class="hljs-params">err,&nbsp;results</span>)&nbsp;</span>{
&nbsp;&nbsp;<span class="hljs-comment">//&nbsp;...</span>
});</pre><p>
	另外，你还可以用 ?? 作为占位符来替代你想要转义的标识符，如：</p><pre class="brush:js;toolbar: true; auto-links: false; hljs javascript"><span class="hljs-keyword">var</span>&nbsp;userId&nbsp;=&nbsp;<span class="hljs-number">1</span>;
<span class="hljs-keyword">var</span>&nbsp;columns&nbsp;=&nbsp;[<span class="hljs-string">'username'</span>,&nbsp;<span class="hljs-string">'email'</span>];
<span class="hljs-keyword">var</span>&nbsp;query&nbsp;=&nbsp;connection.query(<span class="hljs-string">'SELECT&nbsp;??&nbsp;FROM&nbsp;??&nbsp;WHERE&nbsp;id&nbsp;=&nbsp;?'</span>,&nbsp;[columns,&nbsp;<span class="hljs-string">'users'</span>,&nbsp;userId],&nbsp;<span class="hljs-function"><span class="hljs-keyword">function</span>(<span class="hljs-params">err,&nbsp;results</span>)&nbsp;</span>{
&nbsp;&nbsp;<span class="hljs-comment">//&nbsp;...</span>
});
<span class="hljs-built_in">console</span>.log(query.sql);&nbsp;<span class="hljs-comment">//&nbsp;SELECT&nbsp;`username`,&nbsp;`email`&nbsp;FROM&nbsp;`users`&nbsp;WHERE&nbsp;id&nbsp;=&nbsp;1</span></pre><p><strong>请注意，最后这部分的顺序是实验性的，其句法可能在之后的版本中改变。&nbsp;</strong></p><p>
	当你将对象传给 .escape() 或 .query()时，为了防止SQL注入，对象的键将被 .escapeId() 转义。</p>
                                                                                     <p><strong>预查询</strong></p><p>你可以使用 mysql.format 来创建一个多插入点的查询语句，对id和值可以使用适当的转义处理 。下面是一个简单的例子:</p><pre class="brush:js;toolbar: true; auto-links: false; hljs nginx"><span class="hljs-attribute">var</span>&nbsp;sql&nbsp;=&nbsp;<span class="hljs-string">"SELECT&nbsp;*&nbsp;FROM&nbsp;??&nbsp;WHERE&nbsp;??&nbsp;=&nbsp;?"</span>;
<span class="hljs-attribute">var</span>&nbsp;inserts&nbsp;=&nbsp;[<span class="hljs-string">'users'</span>,&nbsp;<span class="hljs-string">'id'</span>,&nbsp;userId];
<span class="hljs-attribute">sql</span>&nbsp;=&nbsp;mysql.format(sql,&nbsp;inserts);</pre><p>这样你就获得了一个有效并且安全的查询语句，然后可以把它发送给数据库。如果你希望在发送给数据库之前就准备好查询语句，这种方法很有用。因为mysql.format是从sqlString.format派生而来的，所以你还可以选择传递stringifyObject 和timezone对象(但不强制要求)，并且允许您通过自定义的方式将对象转换成字符串，以及特定地区的时间和时区(location specific/timezone aware Date).</p><h3><strong>自定义格式</strong></h3><p>如果你想使用其他样式的转义格式，你可以在连接配置选项中自定义一个自定义的格式函数。如果你还想使用内置的escape()函数或其它的连接函数，可以访问connection对象。<br>下面是一个实现自定义格式的例子：</p><pre class="brush:js;toolbar: true; auto-links: false; hljs javascript">connection.config.queryFormat&nbsp;=&nbsp;<span class="hljs-function"><span class="hljs-keyword">function</span>&nbsp;(<span class="hljs-params">query,&nbsp;values</span>)&nbsp;</span>{
&nbsp;&nbsp;<span class="hljs-keyword">if</span>&nbsp;(!values)&nbsp;<span class="hljs-keyword">return</span>&nbsp;query;
&nbsp;&nbsp;<span class="hljs-keyword">return</span>&nbsp;query.replace(<span class="hljs-regexp">/\:(\w+)/g</span>,&nbsp;<span class="hljs-function"><span class="hljs-keyword">function</span>&nbsp;(<span class="hljs-params">txt,&nbsp;key</span>)&nbsp;</span>{
&nbsp;&nbsp;&nbsp;&nbsp;<span class="hljs-keyword">if</span>&nbsp;(values.hasOwnProperty(key))&nbsp;{
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;<span class="hljs-keyword">return</span>&nbsp;<span class="hljs-keyword">this</span>.escape(values[key]);
&nbsp;&nbsp;&nbsp;&nbsp;}
&nbsp;&nbsp;&nbsp;&nbsp;<span class="hljs-keyword">return</span>&nbsp;txt;
&nbsp;&nbsp;}.bind(<span class="hljs-keyword">this</span>));
};
connection.query(<span class="hljs-string">"UPDATE&nbsp;posts&nbsp;SET&nbsp;title&nbsp;=&nbsp;:title"</span>,&nbsp;{&nbsp;<span class="hljs-attr">title</span>:&nbsp;<span class="hljs-string">"Hello&nbsp;MySQL"</span>&nbsp;});</pre><p><strong>获取插入行的id</strong></p><p>如果你把一行插入到一个有自增主键的表中，可以这样获得插入的ID：</p><pre class="brush:js;toolbar: true; auto-links: false; hljs javascript">connection.query(<span class="hljs-string">'INSERT&nbsp;INTO&nbsp;posts&nbsp;SET&nbsp;?'</span>,&nbsp;{<span class="hljs-attr">title</span>:&nbsp;<span class="hljs-string">'test'</span>},&nbsp;<span class="hljs-function"><span class="hljs-keyword">function</span>(<span class="hljs-params">err,&nbsp;result</span>)&nbsp;</span>{
&nbsp;&nbsp;<span class="hljs-keyword">if</span>&nbsp;(err)&nbsp;<span class="hljs-keyword">throw</span>&nbsp;err;
&nbsp;&nbsp;<span class="hljs-built_in">console</span>.log(result.insertId);
});</pre><p>当处理大数字的时候(超过javascript的Number类型的精度),你需要启用supportBigNumbers选项，以便把插入行的ID作为字符串类型读取出来，否者会抛出一个错误。</p><p>当从数据库中查询的数字是一个big number类型的时候，也需要把supportBigNumbers选项设置为true，否则查询出来的数据由于精度限制，会进行四舍五入。</p>
                                                                                     <p><strong>取得受影响的行数</strong></p><p>你可以从 insert，update 或者 delete 子句中取得受影响的行数。</p><pre class="brush:js;toolbar: true; auto-links: false; hljs javascript">connection.query(<span class="hljs-string">'DELETE&nbsp;FROM&nbsp;posts&nbsp;WHERE&nbsp;title&nbsp;=&nbsp;"wrong"'</span>,&nbsp;<span class="hljs-function"><span class="hljs-keyword">function</span>&nbsp;(<span class="hljs-params">err,&nbsp;result</span>)&nbsp;</span>{
&nbsp;&nbsp;<span class="hljs-keyword">if</span>&nbsp;(err)&nbsp;<span class="hljs-keyword">throw</span>&nbsp;err;
&nbsp;&nbsp;<span class="hljs-built_in">console</span>.log(<span class="hljs-string">'deleted&nbsp;'</span>&nbsp;+&nbsp;result.affectedRows&nbsp;+&nbsp;<span class="hljs-string">'&nbsp;rows'</span>);
})</pre><p><strong>取得被改变的行数</strong></p><p>你可以从&nbsp;update 子句取得被改变的行数。</p><p>"被改变的行数" 不同于 "受影响行数" ，它不更新行数，而是改变值。</p><pre class="brush:js;toolbar: true; auto-links: false; hljs javascript">connection.query(<span class="hljs-string">'UPDATE&nbsp;posts&nbsp;SET&nbsp;...'</span>,&nbsp;<span class="hljs-function"><span class="hljs-keyword">function</span>&nbsp;(<span class="hljs-params">err,&nbsp;result</span>)&nbsp;</span>{
&nbsp;&nbsp;<span class="hljs-keyword">if</span>&nbsp;(err)&nbsp;<span class="hljs-keyword">throw</span>&nbsp;err;
&nbsp;&nbsp;<span class="hljs-built_in">console</span>.log(<span class="hljs-string">'changed&nbsp;'</span>&nbsp;+&nbsp;result.changedRows&nbsp;+&nbsp;<span class="hljs-string">'&nbsp;rows'</span>);
})</pre><p><strong>获取连接ID</strong></p><p>你可以取得MySQL的连接ID（“线程ID”），这是一个给定的连接，使用的是线程ID属性。</p><pre class="brush:js;toolbar: true; auto-links: false; hljs javascript">connection.connect(<span class="hljs-function"><span class="hljs-keyword">function</span>(<span class="hljs-params">err</span>)&nbsp;</span>{
&nbsp;&nbsp;<span class="hljs-keyword">if</span>&nbsp;(err)&nbsp;<span class="hljs-keyword">throw</span>&nbsp;err;
&nbsp;&nbsp;<span class="hljs-built_in">console</span>.log(<span class="hljs-string">'connected&nbsp;as&nbsp;id&nbsp;'</span>&nbsp;+&nbsp;connection.threadId);
});</pre><p><strong>并行执行查询</strong></p><p>MySQL协议是顺序的，这意味着你需要多次连接执行并行查询。你可以使用池来管理连接，一个简单的办法是每传入一个http请求，就创建一个连接。</p>
                                                                                     <h3>流式查询行</h3><p>有的时候可能需要查询大量的数据行，然后在接收到这些数据行的时候一行行的处理它们。就像这样：</p><pre class="brush:js; toolbar: true; auto-links: false; hljs javascript"><span class="hljs-keyword">var</span>&nbsp;query&nbsp;=&nbsp;connection.query(<span class="hljs-string">'SELECT&nbsp;*&nbsp;FROM&nbsp;posts'</span>);
query
&nbsp;&nbsp;.on(<span class="hljs-string">'error'</span>,&nbsp;<span class="hljs-function"><span class="hljs-keyword">function</span>(<span class="hljs-params">err</span>)&nbsp;</span>{
&nbsp;&nbsp;&nbsp;&nbsp;<span class="hljs-comment">//&nbsp;处理错误，这之后会触发&nbsp;'end'&nbsp;事件</span>
&nbsp;&nbsp;})
&nbsp;&nbsp;.on(<span class="hljs-string">'fields'</span>,&nbsp;<span class="hljs-function"><span class="hljs-keyword">function</span>(<span class="hljs-params">fields</span>)&nbsp;</span>{
&nbsp;&nbsp;&nbsp;&nbsp;<span class="hljs-comment">//&nbsp;字段信息</span>
&nbsp;&nbsp;})
&nbsp;&nbsp;.on(<span class="hljs-string">'result'</span>,&nbsp;<span class="hljs-function"><span class="hljs-keyword">function</span>(<span class="hljs-params">row</span>)&nbsp;</span>{
&nbsp;&nbsp;&nbsp;&nbsp;<span class="hljs-comment">//&nbsp;暂停连接。如果你的处理过程涉及到&nbsp;I/O&nbsp;操作，这会很有用。</span>
&nbsp;&nbsp;&nbsp;&nbsp;connection.pause();
&nbsp;&nbsp;&nbsp;&nbsp;processRow(row,&nbsp;<span class="hljs-function"><span class="hljs-keyword">function</span>(<span class="hljs-params"></span>)&nbsp;</span>{
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;connection.resume();
&nbsp;&nbsp;&nbsp;&nbsp;});
&nbsp;&nbsp;})
&nbsp;&nbsp;.on(<span class="hljs-string">'end'</span>,&nbsp;<span class="hljs-function"><span class="hljs-keyword">function</span>(<span class="hljs-params"></span>)&nbsp;</span>{
&nbsp;&nbsp;&nbsp;&nbsp;<span class="hljs-comment">//&nbsp;所有数据行都已经接收完毕</span>
&nbsp;&nbsp;});</pre><p>在上面的示例中，有几点需要注意：</p><ul><li><p>通常会在接收到一定数量的数据行之后使用 pause() 来暂停连接。这个数量取决于数据行数量和大小。</p></li><li><p>pause()/resume() 操作基于套接字和解析器。在调用 pause() 后可以保证不会继续触发&nbsp;'result' 事件。</p></li><li><p><strong>一定不能</strong>给流式查询行的 query() 调用提供回调函数。</p></li><li><p>查询到数据行会触发 'result'，INSERT/UPDATE 查询成功的时候也会触发 'result'</p></li><li><p>非常重要一点，不要暂停太久，否则会遇到&nbsp;<em>错误：连接丢失：服务器已断开连接</em>。这个时间限制是在 MySQL 服务器中由&nbsp;net_write_timeout setting 设置的。</p></li></ul><p>此外，你可能需要知道，目前不可能在流式处理过程中处理单独的行列，它们总是完全缓存的。如果你在 MySQL 方面有流式处理大量字段的经验，希望你能在这里分享。</p><h3>通过 Streams2 管道输出</h3><p>查询对象提供了一个便利的方法 .stream([options]) 将查询事件封装在 Readable Streams2 对象中。这个流对象能通过管道简单的流向下游，并基于下游阻塞程度和 highWaterMark 选项自动提供暂停/恢复功能。流对象的 objectMode 参数设置为 true且不可改变（如果你需要一个字节流，那你就得转换流，像示例中的 objstream 那样）。</p><p>示例，通过管道将查询结果输出到另一个流（最大缓冲 5 个对象），这很简单：</p><pre class="brush:js; toolbar: true; auto-links: false; hljs sql">connection.query('<span class="hljs-keyword">SELECT</span>&nbsp;*&nbsp;<span class="hljs-keyword">FROM</span>&nbsp;posts<span class="hljs-string">')
&nbsp;&nbsp;.stream({highWaterMark:&nbsp;5})
&nbsp;&nbsp;.pipe(...);</span></pre><h3>多语句查询</h3><p>由于安全因素（可能因为不正确的转义造成SQL注入攻击），默认情况下不允许多语句查询。不过可以在连接的时候放开这个限制。</p><pre class="brush:js; toolbar: true; auto-links: false; hljs javascript"><span class="hljs-keyword">var</span>&nbsp;connection&nbsp;=&nbsp;mysql.createConnection({<span class="hljs-attr">multipleStatements</span>:&nbsp;<span class="hljs-literal">true</span>});</pre><p>一旦设置允许，就可以像执行其它查询那样执行多语句查询：</p><pre class="brush:js; toolbar: true; auto-links: false; hljs javascript">connection.query(<span class="hljs-string">'SELECT&nbsp;1;&nbsp;SELECT&nbsp;2'</span>,&nbsp;<span class="hljs-function"><span class="hljs-keyword">function</span>(<span class="hljs-params">err,&nbsp;results</span>)&nbsp;</span>{
&nbsp;&nbsp;<span class="hljs-keyword">if</span>&nbsp;(err)&nbsp;<span class="hljs-keyword">throw</span>&nbsp;err;
&nbsp;&nbsp;<span class="hljs-comment">//&nbsp;`results`&nbsp;is&nbsp;an&nbsp;array&nbsp;with&nbsp;one&nbsp;element&nbsp;for&nbsp;every&nbsp;statement&nbsp;in&nbsp;the&nbsp;query:</span>
&nbsp;&nbsp;<span class="hljs-built_in">console</span>.log(results[<span class="hljs-number">0</span>]);&nbsp;<span class="hljs-comment">//&nbsp;[{1:&nbsp;1}]</span>
&nbsp;&nbsp;<span class="hljs-built_in">console</span>.log(results[<span class="hljs-number">1</span>]);&nbsp;<span class="hljs-comment">//&nbsp;[{2:&nbsp;2}]</span>
});</pre><p>除此之外，也可以流式处理多语句查询的返回结果：</p><pre class="brush:js; toolbar: true; auto-links: false; hljs javascript"><span class="hljs-keyword">var</span>&nbsp;query&nbsp;=&nbsp;connection.query(<span class="hljs-string">'SELECT&nbsp;1;&nbsp;SELECT&nbsp;2'</span>);
query
&nbsp;&nbsp;.on(<span class="hljs-string">'fields'</span>,&nbsp;<span class="hljs-function"><span class="hljs-keyword">function</span>(<span class="hljs-params">fields,&nbsp;index</span>)&nbsp;</span>{
&nbsp;&nbsp;&nbsp;&nbsp;<span class="hljs-comment">//&nbsp;the&nbsp;fields&nbsp;for&nbsp;the&nbsp;result&nbsp;rows&nbsp;that&nbsp;follow</span>
&nbsp;&nbsp;})
&nbsp;&nbsp;.on(<span class="hljs-string">'result'</span>,&nbsp;<span class="hljs-function"><span class="hljs-keyword">function</span>(<span class="hljs-params">row,&nbsp;index</span>)&nbsp;</span>{
&nbsp;&nbsp;&nbsp;&nbsp;<span class="hljs-comment">//&nbsp;index&nbsp;refers&nbsp;to&nbsp;the&nbsp;statement&nbsp;this&nbsp;result&nbsp;belongs&nbsp;to&nbsp;(starts&nbsp;at&nbsp;0)</span>
&nbsp;&nbsp;});</pre><p>如果查询中的某一条语句产生错误，则 Error 对象会有一个 err.index 属性用于指出是第几条语句导致的错误。一旦有错误发生，MySQL 会停止执行剩下的语句。</p><p>注意，流式处理多语句查询的接口是试验性的，我期待着对它的反馈。</p>
                                                                                     <p><strong>存储过程</strong></p><p>
	你可以调用你的存储过程，这来自于你的查询，就像调用任何 mysql 驱动一样。如果存储过程产生多个结果集，作为多个语句查询的结果，它们会以同样的方式暴露在你面前。</p><p><strong>join 重叠的列名</strong></p><p>
	当执行&nbsp;join 操作时，你可以取得类似的结果集来重叠列名。</p><p>
	默认的方式是，node-mysql 将会按顺序覆盖从 MySQL 接收到的重叠列名，这会导致一些接收到的值不可用。</p><p>
	另外，你也可以指定你想要的列，嵌套以下这样的表名：</p><pre class="brush:js;toolbar: true; auto-links: false; hljs javascript"><span class="hljs-keyword">var</span>&nbsp;options&nbsp;=&nbsp;{<span class="hljs-attr">sql</span>:&nbsp;<span class="hljs-string">'...'</span>,&nbsp;<span class="hljs-attr">nestTables</span>:&nbsp;<span class="hljs-literal">true</span>};
connection.query(options,&nbsp;<span class="hljs-function"><span class="hljs-keyword">function</span>(<span class="hljs-params">err,&nbsp;results</span>)&nbsp;</span>{
&nbsp;&nbsp;<span class="hljs-comment">/*&nbsp;results&nbsp;will&nbsp;be&nbsp;an&nbsp;array&nbsp;like&nbsp;this&nbsp;now:
&nbsp;&nbsp;[{
&nbsp;&nbsp;&nbsp;&nbsp;table1:&nbsp;{
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;fieldA:&nbsp;'...',
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;fieldB:&nbsp;'...',
&nbsp;&nbsp;&nbsp;&nbsp;},
&nbsp;&nbsp;&nbsp;&nbsp;table2:&nbsp;{
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;fieldA:&nbsp;'...',
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;fieldB:&nbsp;'...',
&nbsp;&nbsp;&nbsp;&nbsp;},
&nbsp;&nbsp;},&nbsp;...]
&nbsp;&nbsp;*/</span>
});</pre><p>
	或者，使用一个字符串来分隔你合并的结果。</p><pre class="brush:js;toolbar: true; auto-links: false; hljs javascript"><span class="hljs-keyword">var</span>&nbsp;options&nbsp;=&nbsp;{<span class="hljs-attr">sql</span>:&nbsp;<span class="hljs-string">'...'</span>,&nbsp;<span class="hljs-attr">nestTables</span>:&nbsp;<span class="hljs-string">'_'</span>};
connection.query(options,&nbsp;<span class="hljs-function"><span class="hljs-keyword">function</span>(<span class="hljs-params">err,&nbsp;results</span>)&nbsp;</span>{
&nbsp;&nbsp;<span class="hljs-comment">/*&nbsp;results&nbsp;will&nbsp;be&nbsp;an&nbsp;array&nbsp;like&nbsp;this&nbsp;now:
&nbsp;&nbsp;[{
&nbsp;&nbsp;&nbsp;&nbsp;table1_fieldA:&nbsp;'...',
&nbsp;&nbsp;&nbsp;&nbsp;table1_fieldB:&nbsp;'...',
&nbsp;&nbsp;&nbsp;&nbsp;table2_fieldA:&nbsp;'...',
&nbsp;&nbsp;&nbsp;&nbsp;table2_fieldB:&nbsp;'...',
&nbsp;&nbsp;},&nbsp;...]
&nbsp;&nbsp;*/</span>
});</pre><p><strong>事务</strong></p><p>在连接级别，简单事务支持是有效的：</p><pre class="brush:js;toolbar: true; auto-links: false; hljs javascript">connection.beginTransaction(<span class="hljs-function"><span class="hljs-keyword">function</span>(<span class="hljs-params">err</span>)&nbsp;</span>{
&nbsp;&nbsp;<span class="hljs-keyword">if</span>&nbsp;(err)&nbsp;{&nbsp;<span class="hljs-keyword">throw</span>&nbsp;err;&nbsp;}
&nbsp;&nbsp;connection.query(<span class="hljs-string">'INSERT&nbsp;INTO&nbsp;posts&nbsp;SET&nbsp;title=?'</span>,&nbsp;title,&nbsp;<span class="hljs-function"><span class="hljs-keyword">function</span>(<span class="hljs-params">err,&nbsp;result</span>)&nbsp;</span>{
&nbsp;&nbsp;&nbsp;&nbsp;<span class="hljs-keyword">if</span>&nbsp;(err)&nbsp;{
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;<span class="hljs-keyword">return</span>&nbsp;connection.rollback(<span class="hljs-function"><span class="hljs-keyword">function</span>(<span class="hljs-params"></span>)&nbsp;</span>{
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;<span class="hljs-keyword">throw</span>&nbsp;err;
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;});
&nbsp;&nbsp;&nbsp;&nbsp;}
&nbsp;&nbsp;&nbsp;&nbsp;<span class="hljs-keyword">var</span>&nbsp;log&nbsp;=&nbsp;<span class="hljs-string">'Post&nbsp;'</span>&nbsp;+&nbsp;result.insertId&nbsp;+&nbsp;<span class="hljs-string">'&nbsp;added'</span>;
&nbsp;&nbsp;&nbsp;&nbsp;connection.query(<span class="hljs-string">'INSERT&nbsp;INTO&nbsp;log&nbsp;SET&nbsp;data=?'</span>,&nbsp;log,&nbsp;<span class="hljs-function"><span class="hljs-keyword">function</span>(<span class="hljs-params">err,&nbsp;result</span>)&nbsp;</span>{
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;<span class="hljs-keyword">if</span>&nbsp;(err)&nbsp;{
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;<span class="hljs-keyword">return</span>&nbsp;connection.rollback(<span class="hljs-function"><span class="hljs-keyword">function</span>(<span class="hljs-params"></span>)&nbsp;</span>{
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;<span class="hljs-keyword">throw</span>&nbsp;err;
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;});
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;}&nbsp;&nbsp;
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;connection.commit(<span class="hljs-function"><span class="hljs-keyword">function</span>(<span class="hljs-params">err</span>)&nbsp;</span>{
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;<span class="hljs-keyword">if</span>&nbsp;(err)&nbsp;{
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;<span class="hljs-keyword">return</span>&nbsp;connection.rollback(<span class="hljs-function"><span class="hljs-keyword">function</span>(<span class="hljs-params"></span>)&nbsp;</span>{
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;<span class="hljs-keyword">throw</span>&nbsp;err;
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;});
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;}
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;<span class="hljs-built_in">console</span>.log(<span class="hljs-string">'success!'</span>);
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;});
&nbsp;&nbsp;&nbsp;&nbsp;});
&nbsp;&nbsp;});
});</pre><p>
	请记住&nbsp;beginTransaction()，&nbsp;commit() 和&nbsp;rollback() 是简单方便的函数，这些函数分别执行&nbsp;START TRANSACTION，COMMIT 和 ROLLBACK 命令。重要的是要理解在&nbsp;MySQL 中的很多函数所引发的隐含提交，这在<a href="http://dev.mysql.com/doc/refman/5.5/en/implicit-commit.html" target="_blank" rel="nofollow">MySQL文档</a>中是有相应的描述的。</p>
                                                                                     <p><strong>Ping</strong> </p><p>
	连接可以使用connection.ping方法向服务器发送一个ping包，若服务器作出响应，将触发回调函数。如果有错误发生，err参数将会传递给回调函数。</p><pre class="brush:js;toolbar: true; auto-links: false; hljs javascript">connection.ping(<span class="hljs-function"><span class="hljs-keyword">function</span>&nbsp;(<span class="hljs-params">err</span>)&nbsp;</span>{
&nbsp;&nbsp;<span class="hljs-keyword">if</span>&nbsp;(err)&nbsp;<span class="hljs-keyword">throw</span>&nbsp;err;
&nbsp;&nbsp;<span class="hljs-built_in">console</span>.log(<span class="hljs-string">'Server&nbsp;responded&nbsp;to&nbsp;ping'</span>);
})</pre><p><strong>超时</strong> </p><p>
	每个操作都可以使用一个可选的固定超时（timeout&nbsp;）选项。这样你就可以为操作指定合适的超时时间。要注意的是超时并非MySQL 协议的内容，也不会通过客户端执行，这一点很重要。也就是说，当达到超时时间时，对应的连接将被终止，也不会再执行其它的操作。</p><pre class="brush:js;toolbar: true; auto-links: false; hljs javascript"><span class="hljs-comment">//&nbsp;60秒后终止查询</span>
connection.query({<span class="hljs-attr">sql</span>:&nbsp;<span class="hljs-string">'SELECT&nbsp;COUNT(*)&nbsp;AS&nbsp;count&nbsp;FROM&nbsp;big_table'</span>,&nbsp;<span class="hljs-attr">timeout</span>:&nbsp;<span class="hljs-number">60000</span>},&nbsp;<span class="hljs-function"><span class="hljs-keyword">function</span>&nbsp;(<span class="hljs-params">err,&nbsp;rows</span>)&nbsp;</span>{
&nbsp;&nbsp;<span class="hljs-keyword">if</span>&nbsp;(err&nbsp;&amp;&amp;&nbsp;err.code&nbsp;===&nbsp;<span class="hljs-string">'PROTOCOL_SEQUENCE_TIMEOUT'</span>)&nbsp;{
&nbsp;&nbsp;&nbsp;&nbsp;<span class="hljs-keyword">throw</span>&nbsp;<span class="hljs-keyword">new</span>&nbsp;<span class="hljs-built_in">Error</span>(<span class="hljs-string">'too&nbsp;long&nbsp;to&nbsp;count&nbsp;table&nbsp;rows!'</span>);
&nbsp;&nbsp;}
&nbsp;&nbsp;<span class="hljs-keyword">if</span>&nbsp;(err)&nbsp;{
&nbsp;&nbsp;&nbsp;&nbsp;<span class="hljs-keyword">throw</span>&nbsp;err;
&nbsp;&nbsp;}
&nbsp;&nbsp;<span class="hljs-built_in">console</span>.log(rows[<span class="hljs-number">0</span>].count&nbsp;+&nbsp;<span class="hljs-string">'&nbsp;rows'</span>);
});</pre><p><strong>错误处理</strong> </p><p>
	这个模块自带了一种统一的错误处理方法，你必须仔细查看以便写出健壮的应用程序。</p><p>
	该模块创建的大多数错误都是JavaScript的Error对象实例。另外，它们通常还会附带两个额外的属性：</p><ul><li><p>
			err.code: &nbsp;<a href="http://dev.mysql.com/doc/refman/5.5/en/error-messages-server.html" target="_blank" rel="nofollow">MySQL服务器错误</a>&nbsp;(比如'ER_ACCESS_DENIED_ERROR'), Node.js错误(比如'ECONNREFUSED')或者内部错误(比如'PROTOCOL_CONNECTION_LOST').		</p></li><li><p>
			err.fatal: 布尔型, 指出这个错误是否是终端连接对象。&nbsp;如果错误不是来自MySQL协议操作，那这个属性不被定义。		</p></li></ul><p>
	致命错误会挂起所有的回调。在下面的例子中，试图连接到一个无效端口时触发一个致命错误。因此错误对象传递到了两个回调中：</p><pre class="brush:js;toolbar: true; auto-links: false; hljs javascript"><span class="hljs-keyword">var</span>&nbsp;connection&nbsp;=&nbsp;<span class="hljs-built_in">require</span>(<span class="hljs-string">'mysql'</span>).createConnection({
&nbsp;&nbsp;<span class="hljs-attr">port</span>:&nbsp;<span class="hljs-number">84943</span>,&nbsp;<span class="hljs-comment">//&nbsp;WRONG&nbsp;PORT</span>
});
connection.connect(<span class="hljs-function"><span class="hljs-keyword">function</span>(<span class="hljs-params">err</span>)&nbsp;</span>{
&nbsp;&nbsp;<span class="hljs-built_in">console</span>.log(err.code);&nbsp;<span class="hljs-comment">//&nbsp;'ECONNREFUSED'</span>
&nbsp;&nbsp;<span class="hljs-built_in">console</span>.log(err.fatal);&nbsp;<span class="hljs-comment">//&nbsp;true</span>
});
connection.query(<span class="hljs-string">'SELECT&nbsp;1'</span>,&nbsp;<span class="hljs-function"><span class="hljs-keyword">function</span>(<span class="hljs-params">err</span>)&nbsp;</span>{
&nbsp;&nbsp;<span class="hljs-built_in">console</span>.log(err.code);&nbsp;<span class="hljs-comment">//&nbsp;'ECONNREFUSED'</span>
&nbsp;&nbsp;<span class="hljs-built_in">console</span>.log(err.fatal);&nbsp;<span class="hljs-comment">//&nbsp;true</span>
});</pre><p>
	然而，正常的错误只返回给属于他们的回调函数。所以在下面的例子中，只有第一个回调会接收错误，第二个查询的会出错：</p><pre class="brush:js;toolbar: true; auto-links: false; hljs javascript">connection.query(<span class="hljs-string">'USE&nbsp;name_of_db_that_does_not_exist'</span>,&nbsp;<span class="hljs-function"><span class="hljs-keyword">function</span>(<span class="hljs-params">err,&nbsp;rows</span>)&nbsp;</span>{
&nbsp;&nbsp;<span class="hljs-built_in">console</span>.log(err.code);&nbsp;<span class="hljs-comment">//&nbsp;'ER_BAD_DB_ERROR'</span>
});
connection.query(<span class="hljs-string">'SELECT&nbsp;1'</span>,&nbsp;<span class="hljs-function"><span class="hljs-keyword">function</span>(<span class="hljs-params">err,&nbsp;rows</span>)&nbsp;</span>{
&nbsp;&nbsp;<span class="hljs-built_in">console</span>.log(err);&nbsp;<span class="hljs-comment">//&nbsp;null</span>
&nbsp;&nbsp;<span class="hljs-built_in">console</span>.log(rows.length);&nbsp;<span class="hljs-comment">//&nbsp;1</span>
});</pre><p>
	最后但并非不重要：如果发生了一个致命的错误，但没有一个对应的回调，或者发生了一个正常的错误但没有属于他的回调，那么错误会向连接对象发出'error'&nbsp;事件。下面的例子说明了这一点：</p><pre class="brush:js;toolbar: true; auto-links: false; hljs javascript">connection.on(<span class="hljs-string">'error'</span>,&nbsp;<span class="hljs-function"><span class="hljs-keyword">function</span>(<span class="hljs-params">err</span>)&nbsp;</span>{
&nbsp;&nbsp;<span class="hljs-built_in">console</span>.log(err.code);&nbsp;<span class="hljs-comment">//&nbsp;'ER_BAD_DB_ERROR'</span>
});
connection.query(<span class="hljs-string">'USE&nbsp;name_of_db_that_does_not_exist'</span>);</pre><p>
	注意：error事件在 node中很特殊。如果他们出现而没有绑定侦听器，会打印堆栈跟踪并杀死你的进程。</p><p><strong>tl;dr</strong>: 这个模块不需要你处理那些静态错误。你总应当给你的方法调用提供回调函数。如果你想忽略这条建议并抑制掉未处理的错误，可以这样做：</p><pre class="brush:js;toolbar: true; auto-links: false; hljs javascript"><span class="hljs-comment">//&nbsp;I&nbsp;am&nbsp;Chuck&nbsp;Norris:</span>
connection.on(<span class="hljs-string">'error'</span>,&nbsp;<span class="hljs-function"><span class="hljs-keyword">function</span>(<span class="hljs-params"></span>)&nbsp;</span>{});</pre>
                                                                                     <p><strong>异常（exception）的安全</strong></p><p>
	这个模块是异常（exception）安全的。这意味着你可以持续使用它，它是你的一个回调函数，它会抛出一个错误，你可以使用'uncaughtException'或者一个域来捕获它。</p><p><strong>类型测试</strong></p><p>
	为了方便起见，这个驱动将会抛出 mysql 类型给本地的&nbsp;JavaScript 默认类型。存在下面这些映射：</p><p><strong><strong>数字型（</strong>Number）</strong></p><ul><li><p>
			TINYINT		</p></li><li><p>
			SMALLINT		</p></li><li><p>
			INT		</p></li><li><p>
			MEDIUMINT		</p></li><li><p>
			YEAR		</p></li><li><p>
			FLOAT		</p></li><li><p>
			DOUBLE		</p></li></ul><p><strong>日期型（Date）</strong></p><ul><li><p>
			TIMESTAMP		</p></li><li><p>
			DATE		</p></li><li><p>
			DATETIME		</p></li></ul><p><strong>Buffer类型</strong></p><ul><li><p>
			TINYBLOB		</p></li><li><p>
			MEDIUMBLOB		</p></li><li><p>
			LONGBLOB		</p></li><li><p>
			BLOB		</p></li><li><p>
			BINARY		</p></li><li><p>
			VARBINARY		</p></li><li><p>
			BIT (最后一个字节将会填满0)		</p></li></ul><p><strong>字符创类型（String）</strong></p><p><strong>注意</strong>&nbsp;在二进制字符集合中的文本是作为一个 Buffer 返回的，相当于是一个字符串。</p><ul><li><p>
			CHAR		</p></li><li><p>
			VARCHAR		</p></li><li><p>
			TINYTEXT		</p></li><li><p>
			MEDIUMTEXT		</p></li><li><p>
			LONGTEXT		</p></li><li><p>
			TEXT		</p></li><li><p>
			ENUM		</p></li><li><p>
			SET		</p></li><li><p>
			DECIMAL (可能超出浮点数的精度)		</p></li><li><p>
			BIGINT (可能超出浮点数的精度)		</p></li><li><p>
			TIME (可以映射成日期型（Date）, 但是日期是一个集合?)		</p></li><li><p>
			GEOMETRY (从来没使用过，如果你用过请联系我)		</p></li></ul><p>
	不推荐（将来可能消失/改变）关闭类型转换，但是你现在可以做这样的连接：</p><pre class="brush:js;toolbar: true; auto-links: false; hljs javascript"><span class="hljs-keyword">var</span>&nbsp;connection&nbsp;=&nbsp;<span class="hljs-built_in">require</span>(<span class="hljs-string">'mysql'</span>).createConnection({<span class="hljs-attr">typeCast</span>:&nbsp;<span class="hljs-literal">false</span>});</pre><p>
	或者是在查询级别这么做：</p><pre class="brush:js;toolbar: true; auto-links: false; hljs javascript"><span class="hljs-keyword">var</span>&nbsp;options&nbsp;=&nbsp;{<span class="hljs-attr">sql</span>:&nbsp;<span class="hljs-string">'...'</span>,&nbsp;<span class="hljs-attr">typeCast</span>:&nbsp;<span class="hljs-literal">false</span>};
<span class="hljs-keyword">var</span>&nbsp;query&nbsp;=&nbsp;connection.query(options,&nbsp;<span class="hljs-function"><span class="hljs-keyword">function</span>(<span class="hljs-params">err,&nbsp;results</span>)&nbsp;</span>{
});</pre><p>
	你可以始终通过一个函数来处理类型转换。你可以给定一些列信息，就像数据库，表，类型和长度。如果你想申请自定义类型转换成指定的你可以处理的类型，那么回调函数是默认的。这里举个例子，将&nbsp;TINYINT(1) 转换成&nbsp;boolean：</p><pre class="brush:js;toolbar: true; auto-links: false; hljs ruby">connection.query({
&nbsp;&nbsp;<span class="hljs-symbol">sql:</span>&nbsp;<span class="hljs-string">'...'</span>,
&nbsp;&nbsp;<span class="hljs-symbol">typeCast:</span>&nbsp;function&nbsp;(field,&nbsp;<span class="hljs-keyword">next</span>)&nbsp;{
&nbsp;&nbsp;&nbsp;&nbsp;<span class="hljs-keyword">if</span>&nbsp;(field.type&nbsp;==&nbsp;<span class="hljs-string">'TINY'</span>&nbsp;&amp;&amp;&nbsp;field.length&nbsp;==&nbsp;<span class="hljs-number">1</span>)&nbsp;{
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;<span class="hljs-keyword">return</span>&nbsp;(field.string()&nbsp;==&nbsp;<span class="hljs-string">'1'</span>);&nbsp;<span class="hljs-regexp">//</span>&nbsp;<span class="hljs-number">1</span>&nbsp;=&nbsp;<span class="hljs-literal">true</span>,&nbsp;<span class="hljs-number">0</span>&nbsp;=&nbsp;<span class="hljs-literal">false</span>
&nbsp;&nbsp;&nbsp;&nbsp;}
&nbsp;&nbsp;&nbsp;&nbsp;<span class="hljs-keyword">return</span>&nbsp;<span class="hljs-keyword">next</span>();
&nbsp;&nbsp;}
});</pre><p><strong>警告: 在你的自定义类型的回调函数中，你必须调用这三个&nbsp;<strong>field&nbsp;</strong>函数来解析。他们只能被调用一次（查看 讨论 #539）</strong></p><pre class="brush:js;toolbar: true; auto-links: false; hljs css"><span class="hljs-selector-tag">field</span><span class="hljs-selector-class">.string</span>()
<span class="hljs-selector-tag">field</span><span class="hljs-selector-class">.buffer</span>()
<span class="hljs-selector-tag">field</span><span class="hljs-selector-class">.geometry</span>()
<span class="hljs-selector-tag">are</span>&nbsp;<span class="hljs-selector-tag">aliases</span>&nbsp;<span class="hljs-selector-tag">for</span>
<span class="hljs-selector-tag">parser</span><span class="hljs-selector-class">.parseLengthCodedString</span>()
<span class="hljs-selector-tag">parser</span><span class="hljs-selector-class">.parseLengthCodedBuffer</span>()
<span class="hljs-selector-tag">parser</span><span class="hljs-selector-class">.parseGeometryValue</span>()</pre><p>
	你可以通过仔细查找，找到&nbsp;field 函数：</p><p><a href="https://github.com/mysqljs/mysql/blob/master/lib/protocol/packets/RowDataPacket.js#L41" target="_blank" rel="nofollow">RowDataPacket.prototype._typeCast</a></p>
                                                                                     <p><strong>连接标志</strong></p><p>无论何种原因，你要是想改变默认的连接标志，你可以使用连接选项标记。通过逗号分隔字符串，把条目添加到默认的标志中。如果你不想要默认的准备好的标志，你可以用一个负号。添加一个不在默认列表中的标记，仅需要写出标记名称，或者在标记前加一个+号（大小写敏感）。</p><p><strong>请记住一些有用的标记，它们是没有被提供的标记（例如：<strong>Compression</strong>），它们还没有被指定。</strong></p><p><strong>例子</strong></p><p>下面例子中的黑名单&nbsp;FOUND_ROWS 标记 来自默认的连接标记。</p><pre class="brush:php;toolbar: true; auto-links: false; hljs javascript"><span class="hljs-keyword">var</span>&nbsp;connection&nbsp;=&nbsp;mysql.createConnection(<span class="hljs-string">"mysql://localhost/test?flags=-FOUND_ROWS"</span>);</pre><p><strong>默认标记</strong></p><p>下面的标记被发送是通过一个默认的新连接：</p><ul><li><p>CONNECT_WITH_DB - 指定数据库在连接上的能力。</p></li><li><p>FOUND_ROWS - 发送发现affectedRows行而受影响的行。</p></li><li><p>IGNORE_SIGPIPE - 老的；无影响。</p></li><li><p>IGNORE_SPACE - 让解析器忽略空格之前的（查询。&nbsp;</p></li><li><p>LOCAL_FILES - 可以使用 LOAD DATA LOCAL.</p></li><li><p>LONG_FLAG</p></li><li><p>LONG_PASSWORD - 使用改进的老密码验证版本。</p></li><li><p>MULTI_RESULTS - 为 COM_QUERY&nbsp;可以处理多个结果。</p></li><li><p>ODBC Old; 无影响。</p></li><li><p>PROTOCOL_41 - 使用4.1协议。</p></li><li><p>PS_MULTI_RESULTS - 为 COM_STMT_EXECUTE&nbsp;可以处理多个结果。</p></li><li><p>RESERVED - &nbsp;4.1 协议的老标记。&nbsp;</p></li><li><p>SECURE_CONNECTION - 支持本地4.1认证。</p></li><li><p>TRANSACTIONS - 询问事务状态标记。</p></li></ul><p>另外，如果选项&nbsp;multipleStatements&nbsp;设置成 true，下面这些将会被发送：<br></p><ul><li><p>MULTI_STATEMENTS - 每次查询或者准备声明，客户端都可能会发送多个子句。</p></li></ul><p><strong>其他有用的标记</strong></p><p>有其他一些可用的标记。他们可能会也可能不会起作用，但是仍然可用来指定。</p><ul><li><p>COMPRESS</p></li><li><p>INTERACTIVE</p></li><li><p>NO_SCHEMA</p></li><li><p>PLUGIN_AUTH</p></li><li><p>REMEMBER_OPTIONS</p></li><li><p>SSL</p></li><li><p>SSL_VERIFY_SERVER_CERT</p></li></ul><p><strong>调试和报告问题</strong></p><p>如果你遇到问题，你可以在连接时激活调试模式来辅助：</p><pre class="brush:js;toolbar: true; auto-links: false; hljs javascript"><span class="hljs-keyword">var</span>&nbsp;connection&nbsp;=&nbsp;mysql.createConnection({<span class="hljs-attr">debug</span>:&nbsp;<span class="hljs-literal">true</span>});</pre><p>这将打印所有传入和传出的数据包到 stdout 上。你也可以限制调试包的数组类型，并传递至调试：</p><pre class="brush:js;toolbar: true; auto-links: false; hljs javascript"><span class="hljs-keyword">var</span>&nbsp;connection&nbsp;=&nbsp;mysql.createConnection({<span class="hljs-attr">debug</span>:&nbsp;[<span class="hljs-string">'ComQueryPacket'</span>,&nbsp;<span class="hljs-string">'RowDataPacket'</span>]});</pre><p>限制调试查询和数据包。</p><p>如果这篇文章没有帮助到你，你可以打开一个&nbsp;GitHub 问题。一个好的&nbsp;GitHub 问题会是这样：</p><ul><li><p>尽可能减少代码的复制（如果可能的话）</p></li><li><p>尽可能调试输出你的环境信息（mysql 版本，node版本，操作系统，等等）尽你的能力去收集。</p></li></ul>
                                                                                     <p><strong>运行测试</strong></p><p>测试套件分成两个部分：单元测试和集成测试。单元测试运行在任何机器上，而集成测试需要安装一个MySQL服务器实例。</p><p><strong>运行单元测试</strong></p><pre class="brush:shell;toolbar: true; auto-links: false; hljs"><span class="hljs-meta">$</span><span class="bash">&nbsp;FILTER=unit&nbsp;npm&nbsp;<span class="hljs-built_in">test</span></span></pre><p><strong>运行集成测试</strong></p><p>设置环境变量MYSQL_DATABASE，MYSQL_HOST，MYSQL_PORT，MYSQL_USER 和&nbsp;MYSQL_PASSWORD。MYSQL_SOCKET 可以被&nbsp;MYSQL_HOST 和&nbsp;MYSQL_PORT 替代连接至UNIX socket。之后运行&nbsp;npm 测试。</p><p>举例来说，如果你有一个 mysql 安装被运行在&nbsp;localhost:3306 并且没有为 root 用户设置密码，运行：</p><pre class="brush:shell;toolbar: true; auto-links: false; hljs"><span class="hljs-meta">$</span><span class="bash">&nbsp;mysql&nbsp;-u&nbsp;root&nbsp;-e&nbsp;<span class="hljs-string">"CREATE&nbsp;DATABASE&nbsp;IF&nbsp;NOT&nbsp;EXISTS&nbsp;node_mysql_test"</span></span>
<span class="hljs-meta">$</span><span class="bash">&nbsp;MYSQL_HOST=localhost&nbsp;MYSQL_PORT=3306&nbsp;MYSQL_DATABASE=node_mysql_test&nbsp;MYSQL_USER=root&nbsp;MYSQL_PASSWORD=&nbsp;FILTER=integration&nbsp;npm&nbsp;<span class="hljs-built_in">test</span></span></pre><p>那接下来就要做下面这些事：</p><ul><li><p>预处理子句</p></li><li><p>支持的编码格式不止于&nbsp;UTF-8 / ASCII</p></li></ul>
                                        </div>
    <div class="links">
        <p>本文地址：<a href="https://www.oschina.net/translate/node-mysql-tutorial">https://www.oschina.net/translate/node-mysql-tutorial</a></p>
        <p>原文地址：<a href="https://github.com/mysqljs/mysql">https://github.com/mysqljs/mysql</a></p>
    </div>
        <div class="copyright">
        本文中的所有译文仅用于学习和交流目的，转载请务必注明文章译者、出处、和本文链接<br>
        我们的翻译工作遵照 <a href="http://zh.wikipedia.org/wiki/Wikipedia:CC" target="_blank">CC 协议</a>，如果我们的工作有侵犯到您的权益，请及时联系我们
    </div>
    

            
<!-- Generated by oschina (init:1[ms],page:15[ms],ip:221.221.165.249) //--></body></html>